package com.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

public class JDBCUtils {

    private static DataSource ds = null;

    /**
     静态配置文件
     *
     */
    static {
        Properties p = new Properties();
        try {

            p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
            ds = BasicDataSourceFactory.createDataSource(p);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 加载驱动
     *
     * @return
     */
    public static Connection getConn() {
        try {

            return ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 关闭 rs stat conn
     *
     * @param rs
     * @param stat
     * @param conn
     */
    public static void close(ResultSet rs, Statement stat, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (stat != null) {
                    stat.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    /**
     * 关闭rs,ps,conn
     * @param rs
     * @param ps
     * @param conn
     */
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    /**
     * 关闭 stat conn
     *
     * @param stat
     * @param conn
     */
    public static void close(Statement stat, Connection conn) {
        close(null, stat, conn);
    }

    /**
     * 关闭 ps conn
     * @param ps
     * @param conn
     */
    public static void close(PreparedStatement ps, Connection conn) {
        close(null, ps, conn);
    }

    /**
     * 修改sql
     * @param s
     * @param args
     * @return
     */
    public static int update(String s,Object ... args){
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JDBCUtils.getConn();
            ps = conn.prepareStatement(s);
            for (int i = 1; i <= args.length; i++) {
                ps.setObject(i,args[i-1]);
            }
            return ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(ps,conn);
        }
        return 0;
    }

    /**
     * 单列查询  select
     * @param s
     * @param args
     * @return
     */
    public static List selectOne(String s, Object ... args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<Object> list = new ArrayList<>();
        HashMap<Object, Object> map = new HashMap<>();

        try {
            conn = JDBCUtils.getConn();
            ps = conn.prepareStatement(s);
            for (int i = 1; i <= args.length; i++) {
                ps.setObject(i,args[i-1]);
            }
            rs = ps.executeQuery();
            while (rs.next()){
                map = new HashMap<>();
                ResultSetMetaData md = rs.getMetaData();
                int count = md.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    String key = md.getColumnLabel(i);
                    Object value = rs.getObject(key);
                    map.put(key,value);
                }
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
           JDBCUtils.close(rs,ps,conn);
        }
        return list;
    }

    /**
     * 查询全部
     * @param s
     * @return
     */
    public static List selectAll(String s){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        HashMap<Object, Object> map = new HashMap<>();
        ArrayList<Object> list = new ArrayList<>();
        try {
            conn = JDBCUtils.getConn();
            ps = conn.prepareStatement(s);
            rs = ps.executeQuery();

            while (rs.next()){
                map = new HashMap<>();
                ResultSetMetaData md = rs.getMetaData();
                int count = md.getColumnCount();
                for (int i = 1; i <= count; i++) {
                    String key = md.getColumnLabel(i);
                    Object value = rs.getObject(key);
                    map.put(key,value);
                }
                list.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.close(rs,ps,conn);
        }
        return list;
    }

    /**
     * 反射单列查询 query
     * @param s
     * @param c
     * @param args
     * @param <T>
     * @return
     */
//    public static <T> List<T> queryone(String s, Class<T> c,Object ... args){
//        Connection conn = null;
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        ArrayList<T> list = new ArrayList<>();
//        try {
//            conn = JDBCUtils.getConn();
//            ps = conn.prepareStatement(s);
//            for (int i = 1; i <= args.length; i++) {
//                ps.setObject(i,args[i-1]);
//            }
//            rs = ps.executeQuery();
//            while (rs.next()){
//                T t = c.newInstance();
//                ResultSetMetaData rsm = rs.getMetaData();
//                int count = rsm.getColumnCount();
//
//                HashMap<String, Object> map = new HashMap<>();
//                for (int i = 1; i <= count; i++) {
//                    String key = rsm.getColumnLabel(i);
//                    Object value = rs.getObject(key);
//                    map.put(key,value);
//                }
//                BeanUtils.populate(t,map);
//                list.add(t);
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }finally {
//            JDBCUtils.close(rs,ps,conn);
//        }
//        return list;
//    }



    public static <T> List<T> selectone(Class<T> c, String s, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t;
        ArrayList<T> list = new ArrayList<>();

        try {
            conn = JDBCUtils.getConn();
            ps = conn.prepareStatement(s);
            for (int i = 1; i <= args.length; i++) {
                ps.setObject(i, args[i - 1]);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                t = c.newInstance();
                Field[] fields = c.getDeclaredFields();
                Method[] methods = c.getDeclaredMethods();
                for (Field field : fields) {
                    Object object = rs.getObject(field.getName());

                    for (Method method : methods) {
                        StringBuffer sb = new StringBuffer();
                        if (sb.append("set" + field.getName()).toString().equalsIgnoreCase(method.getName())) {
                            method.invoke(t, object);
                        }
                    }
                }
                list.add(t);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, ps, conn);
        }
        return list;
    }


    /**
     * 反射查询所有
     * @param c
     * @param table
     * @param <T>
     * @return
     */
    public static <T> List<T> queryAll(Class<T> c,String table){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        T t;
        ArrayList<T> list = new ArrayList<>();

        try {
            conn = JDBCUtils.getConn();
            String sql = "select * from " + table;
            ps = conn.prepareStatement(sql);

            rs = ps.executeQuery();
            while (rs.next()){
                t = c.newInstance();
                Field[] fields = c.getDeclaredFields();
                Method[] methods = c.getDeclaredMethods();

                for (Field field : fields) {
                    Object object = rs.getObject(field.getName());
//                    System.out.println(object);

                    for (Method method : methods) {
                        StringBuffer sb = new StringBuffer();

                        if(sb.append("set"+field.getName()).toString().equalsIgnoreCase(method.getName())){
                            method.invoke(t,object);
                        }
                    }
                }
                list.add(t);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,ps,conn);
        }
        return list;
    }

    public static void main(String[] args) {

    }
}
